Welcome to Data Modeling Using
Star and Snowflake Schemas. After watching this video, you will be able to: Describe star schema modeling in
terms of facts and dimensions. Describe snowflake schema as
an extension of Star schema. Distinguish star from snowflake
schema in terms of normalization. Recall that a fact table contains foreign keys that refer to the primary
keys of dimension tables. The idea of a star schema is based on the way
a set of dimension tables can be visualized, or modeled, as radiating from a central
fact table, linked by these keys. A star schema is thus a graph, whose
nodes are fact and dimension tables, and whose edges are the
relations between those tables. Star schemas are commonly used to develop
specialized data warehouses called “data marts.” Snowflake schemas are a generalization of star schemas and can be seen as
normalized star schemas. Normalization means separating
the levels or hierarchies of a dimension table into separate child tables. A schema need not be fully normalized
to be considered a snowflake, so long as at least one of its
dimensions has its levels separated. Let’s look at some general principles you need to consider when designing a
data model for a star schema. The first step involves selecting a business
process as the basis for what you want to model. You might be interested in processes such as
sales, manufacturing, or supply chain logistics. In step two, you need to choose a granularity, which is the level of detail
that you need to capture. Are you interested in coarse-grained information
such as annual regional sales numbers? Or, maybe you want to drill down into
monthly sales performance by salesperson. Next in the process, you need
to identify the dimensions. These may include attributes such as the date and
time, and names of people, places, and things. The final consideration in designing
star schemas is to identify the facts. These are the things being
measured in the business process. Let’s apply these considerations to a scenario. Imagine, for example, that you
are a data engineer helping to lay out the data ops for a new store
called “A to Z Discount Warehouse.” They would like you to develop a
data plan to capture everyday POS, or point-of-sales transactions that happen at the till, where customers have their
items scanned and pay for them. Thus, “point-of-sale transactions” is the
business process that you want to model. The finest granularity you can expect to capture
from POS transactions comes from the individual line items, which is included in the detailed
information you can see on a typical store receipt. This is precisely what “A to
Z” is interested in capturing. The next step in the process
is to identify the dimensions. These include attributes such as
the date and time of the purchase, the store name, the products purchased,
and the cashier who processed the items. You might add other dimensions,
like “payment method,” whether the line item is a return or a purchase, and
perhaps a “customer membership number.” Now it’s time to consider the facts. Thus, you identify facts such as
the amount for each item’s price, the quantity of each product sold, any discounts
applied to the sale, and the sales tax applied. Other facts to consider include environmental
fees, or deposit fees for returnable containers. Now you are ready to start building your
star schema for “A to Z Discount Warehouse.” At the center of your star schema
sits a “point-of-sales fact table,” which contains a unique ID, called “P O S
ID,” for each line item in the transaction, plus the following facts, or measures:
the amount of the transaction in dollars, the quantity, or number of items involved,
the sales tax, and any discount applied. There may be other facts to include, but
these can be added later as you discover them. Each line item from a sales transaction
has many dimensions associated with it. You include them as foreign
keys in your fact table, or as links to the primary
keys of your dimension tables. For example, the name of the store at which
the item was sold is kept in a dimension table called “store,” which is identified
in the fact-table by the value of the foreign “Store ID” key, which is
the primary key for the Store table. Product information is
stored in the Product table, which is uniquely identified
by the “ProductID” key. Similarly, the date of the
transaction is keyed by the “Date ID,” which cashier entered the transaction
is keyed by the “Cashier ID,” and which member was involved
is indicated by the “Member ID.” This illustrates what a
star schema might look like. Let’s see how you can use normalization to
extend your star schema to a snowflake schema. Starting with your star schema, you can extract
some of the details of the dimension tables into their own separate dimension
tables, creating a hierarchy of tables. A separate city table can be used to
record which city the store is in, while a foreign ‘city id’ key would be included
in the ‘Store’ table to maintain the link. You might also have tables and keys
for the city’s state or province, and a pre-defined sales region for the store,
and for which country the store resides in. We’ve left out the associated keys for simplicity. We can continue to normalize other
dimensions, like the product’s brand, and a “product category” that it belongs to, the day of week and the month corresponding
to the date, plus the quarter, and so on. This normalized version of the star
schema is called a snowflake schema, due to its multiple layers of branching
which resembles a snowflake pattern. Much like how pointers are used
to point to memory locations in computing, normalization reduces
the memory footprint of the data. In this video, you learned that: Facts and dimension tables, together
with foreign and primary keys, are used to form star and
snowflake modeling schemas. Design considerations for
data modeling with star schema include identifying a business process, its
granularity, and its facts and dimensions. Snowflake schemas can be described
as normalized star schemas, where normalization involves separating
dimension tables into individual tables defined by levels or hierarchies of the parent
dimension and reduces storage footprint.